package com.smart.domain;
import com.smart.dao.DBoperator;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/*老师的增删改查语句*/
public class TeacherUserDaoImpl implements TeacherUserDao {
    @Override
    public void insert(StudentUser studentuser) throws Exception {/*插入学生信息*/
        String sql="insert into stu(name,id,sclass,grade,sex) VALUES (?,?,?,?,?)";
        PreparedStatement pstmt = null;
        DBoperator dbc = null;
        try {
            dbc = new DBoperator();
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.setString(1,studentuser.getName());
            pstmt.setString(2,studentuser.getId());
            pstmt.setString(3,studentuser.getSclass());
            pstmt.setString(4,studentuser.getGrade());
            pstmt.setString(5,studentuser.getSex());
            pstmt.executeUpdate();
            pstmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbc.close();
        }
    }

    @Override
    public void update(StudentUser studentuser)throws Exception {/*修改学生信息*/
        String sql = "update stu set grade=? where id=?";
        PreparedStatement pstmt= null;
        DBoperator dbc = null;
        try {
            dbc = new DBoperator();
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.setString(1,studentuser.getGrade());
            pstmt.setString(2,studentuser.getId());
            pstmt.executeUpdate();
            pstmt.close();
            dbc.close();
        } catch (Exception  e) {
            e.printStackTrace();
        }finally {
            dbc.close();
        }
    }

    @Override
    public void delete(String id) throws Exception {/*删除学生信息*/
        String sql="delete from stu where id=?";
        PreparedStatement pstmt = null;
        DBoperator dbc = null;
        try {
            dbc = new DBoperator();
            pstmt = dbc.getConnection().prepareStatement(sql);
            pstmt.setString(1,id);
            pstmt.executeUpdate();
            pstmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            dbc.close();
        }
    }



    @Override
    public List selectall() throws Exception {/*查询所有学生信息*/
        PreparedStatement pstmt= null;
        DBoperator dbc = null;
        String sql = "select * from stu ";
        try {
            dbc = new DBoperator();
            pstmt = dbc.getConnection().prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            int col = rs.getMetaData().getColumnCount();
            System.out.println("==========================");
            while (rs.next()) {
                for (int i = 1; i <= col; i++) {
                    System.out.print(rs.getString(i) + "\t");
                    if ((i == 2) && (rs.getString(i).length() < 8)) {
                        System.out.print("\t");
                    }
                }
                System.out.println("");
            }
            System.out.println("==========================");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    @Override
    public List<TeacherUser> selectid(String id) {/*按学号查询学生信息*/
        List<TeacherUser> list=new ArrayList<TeacherUser>();
        String SQL="select * from stu where sdept=?";
        DBoperator stmt = null;
        stmt = new DBoperator();
        Connection coon = stmt.getConnection();
        try {
            PreparedStatement statement1 = coon.prepareStatement(SQL);
            statement1.setString(1,id);
            ResultSet resultSet = statement1.executeQuery();
            while(resultSet.next()){
                TeacherUser  user = new TeacherUser();//每一次都要重新new一个
                user.setId(resultSet.getString("number"));
                user.setName(resultSet.getString("id"));
                user.setSex(resultSet.getString("sex"));
                user.setGrade(resultSet.getString("grade"));
                user.setSclass(resultSet.getString("sclass"));
                list.add(user);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}